﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Red5.Frame.Utility.DataBase.MsSql
{
    // <summary>
    /// 接口 IAdoUtility 定义了与 Microsoft SQL Server 进行交互的基本方法。
    /// </summary>
    public interface IAdoUtility
    {
        /// <summary>
        /// 执行SQL查询并返回结果集为一个 DataTable 对象。
        /// </summary>
        /// <param name="sql">SQL 查询语句。</param>
        /// <param name="parameters">可选的参数数组。</param>
        /// <returns>包含查询结果的 DataTable。</returns>
        /// <example>
        /// <code>
        /// var utility = new AdoUtility();
        /// var dt = utility.ExecuteDataTable("SELECT * FROM YourTable", new SqlParameter("@id", 1));
        /// </code>
        /// </example>
        DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters);

        /// <summary>
        /// 执行非查询SQL命令（如 INSERT、UPDATE 或 DELETE），并返回受影响的行数。
        /// </summary>
        /// <param name="sql">SQL 命令。</param>
        /// <param name="parameters">可选的参数数组。</param>
        /// <returns>受影响的行数。</returns>
        /// <example>
        /// <code>
        /// var utility = new AdoUtility();
        /// var rowsAffected = utility.ExecuteNonQuery("UPDATE YourTable SET Column1 = @value WHERE Id = @id", 
        ///     new SqlParameter("@value", "new value"), new SqlParameter("@id", 1));
        /// </code>
        /// </example>
        int ExecuteNonQuery(string sql, params SqlParameter[] parameters);

        /// <summary>
        /// 执行SQL查询并返回结果集的第一行第一列的值（通常用于获取单个标量值）。
        /// </summary>
        /// <param name="sql">SQL 查询语句。</param>
        /// <param name="parameters">可选的参数数组。</param>
        /// <returns>第一行第一列的值。</returns>
        /// <example>
        /// <code>
        /// var utility = new AdoUtility();
        /// var count = (int)utility.ExecuteScalar("SELECT COUNT(*) FROM YourTable");
        /// </code>
        /// </example>
        object ExecuteScalar(string sql, params SqlParameter[] parameters);

        /// <summary>
        /// 在事务中执行一系列 SQL 命令。
        /// </summary>
        /// <param name="actionList">一个 Action<SqlCommand> 数组，每个元素代表一个要执行的命令。</param>
        /// <example>
        /// <code>
        /// var utility = new AdoUtility();
        /// utility.ExecuteInTransaction(new Action<SqlCommand> {
        ///     cmd => { cmd.CommandText = "INSERT INTO YourTable (...) VALUES (...)" ; cmd.ExecuteNonQuery(); },
        ///     cmd => { cmd.CommandText = "UPDATE AnotherTable SET ... WHERE ..."; cmd.ExecuteNonQuery(); }
        /// });
        /// </code>
        /// </example>
        void ExecuteInTransaction(Action<SqlCommand> actionList);

        /// <summary>
        /// 获取数据库内所有表的信息。
        /// </summary>
        /// <returns>包含所有表信息的 DataTable。</returns>
        /// <example>
        /// <code>
        /// var utility = new AdoUtility();
        /// var tables = utility.GetTableInfo();
        /// </code>
        /// </example>
        DataTable GetTableInfo();

        /// <summary>
        /// 获取数据库内所有表字段信息。
        /// </summary>
        /// <returns>包含所有表字段信息的 DataTable。</returns>
        /// <example>
        /// <code>
        /// var utility = new AdoUtility();
        /// var columns = utility.GetTableColumnInfo();
        /// </code>
        /// </example>
        DataTable GetTableColumnInfo();

        /// <summary>
        /// 获取指定表的所有字段信息。
        /// </summary>
        /// <param name="tableName">表名称。</param>
        /// <returns>包含指定表字段信息的 DataTable。</returns>
        /// <example>
        /// <code>
        /// var utility = new AdoUtility();
        /// var columns = utility.GetTableColumnInfo("YourTable");
        /// </code>
        /// </example>
        DataTable GetTableColumnInfo(string tableName);

        /// <summary>
        /// 获取数据库内大多数表都会定义的主键字段名。
        /// </summary>
        /// <returns>主键字段名。</returns>
        /// <example>
        /// <code>
        /// var utility = new AdoUtility();
        /// var pkColumnName = utility.GetPkColumnName();
        /// </code>
        /// </example>
        string GetPkColumnName();

        /// <summary>
        /// 根据表名获取表索引信息。
        /// </summary>
        /// <param name="tableName">表名称。</param>
        /// <returns>包含索引信息的 DataTable。</returns>
        /// <example>
        /// <code>
        /// var utility = new AdoUtility();
        /// var indexes = utility.GetTableIndex("YourTable");
        /// </code>
        /// </example>
        DataTable GetTableIndex(string tableName);

        /// <summary>
        /// 获取指定表的所有触发器信息。
        /// </summary>
        /// <param name="tableName">表名称。</param>
        /// <returns>包含触发器信息的 DataTable。</returns>
        /// <example>
        /// <code>
        /// var utility = new AdoUtility();
        /// var triggers = utility.GetTableTrigger("YourTable");
        /// </code>
        /// </example>
        DataTable GetTableTrigger(string tableName);
    }
}
